package com.laxcen.smoke.dao;

import com.laxcen.smoke.dto.res.AdvertisementAndNoRes;
import com.laxcen.smoke.entity.Advertisement;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface AdvertisementDao {

    @Insert("insert into advertisement(id,adverimg,createtime,updatetime) values(#{id},#{adverimg},#{createtime},#{updatetime})")
    int insertAdv(String id, String adverimg, Date createtime, Date updatetime);

    @Insert("insert into real_smart_advertisement(id,no,adverid,smartid,createtime) values(#{id},#{no},#{adverid},#{smartid},#{createtime})")
    int insertRealAdv(String id, String no, String adverid, String smartid, Date createtime);

    @Delete("delete from advertisement where id = #{adverid}")
    int deleteRealAdv(String adverid);

    @Update("update advertisement set adverimg = #{adverimg},updatetime = #{updatetime} where id = #{id}")
    int updateAdv(String adverimg, Date updatetime, String id);

    @Update("update real_smart_advertisement rsa left join smart sm on rsa.Smartid = sm.id  set rsa.smartid = #{smartid} where rsa.adverid = #{adverid}")
    int updateAdvReal(String smartid, String adverid);

//    @Select("select rsa.smartid  from real_smart_advertisement rsa left join smart sm on rsa.smartid = sm.id where sm.no = #{smartno}")
//    String getSmartidBySmNo(String smartno);

    @Select("select * from advertisement order by updatetime desc")
    List<AdvertisementAndNoRes> getAdvList();

    @Select("select distinct adv.*,sm.no smartno from real_smart_advertisement rsa left join advertisement adv on rsa.Adverid = adv.id left join smart sm on rsa.Smartid = sm.id where sm.no = #{smartno} order by adv.updatetime desc")
    List<AdvertisementAndNoRes> getAdvListBySmartno(String smartno);

    @Select("select * from advertisement where createtime between #{starttime} and #{endtime} order by createtime desc")
    List<AdvertisementAndNoRes> getAdvListByCreatetime(Date starttime, Date endtime);

    @Select("select distinct adv.*,sm.no smartno from real_smart_advertisement rsa left join advertisement adv on rsa.Adverid = adv.id left join smart sm on rsa.Smartid = sm.id where sm.no = #{smartno} and adv.createtime between #{starttime} and #{endtime} order by adv.updatetime desc")
    List<AdvertisementAndNoRes> getAdvListByAllCondition(String smartno, Date starttime, Date endtime);

    @Select("select * from advertisement where id = #{id}")
    AdvertisementAndNoRes getOneAdv(String id);

    @Select("select id from smart where no = #{smartno}")
    String getSmartId(String smartno);

    @Select("select adv.* from real_smart_advertisement rsa left join advertisement adv on rsa.Adverid = adv.id where rsa.Smartid = #{smartid}")
    Advertisement getAdverBySmId(String smartid);


}
